package Demo2.dao;
import Demo2.entity.Student;
import Demo2.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    //查看全部学生表
    public List<Student> stuAll(){
        List<Student> list =new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        String sql ="select * from s_student";
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Student st =new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                list.add(st);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(rs,ps,conn);
        }
        return list;
    }

    //增加
    public int add(Student s){
        Connection conn =null;
        PreparedStatement ps =null;
        try {
            conn = DBUtil.getConnection();
            String sql ="INSERT INTO `s_student` (`sname`, `sex`, `telephone`, `qq`, `wechat`, `gradeid`, `enterdate`, `fromcity`, `birthday`, `address`, `pass`, `idcardpic`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            ps =conn.prepareStatement(sql);
            ps.setString(1,s.getSname());
            ps.setString(2,s.getSex());
            ps.setString(3,s.getTelephone());
            ps.setString(4,s.getQq());
            ps.setString(5,s.getWechat());
            ps.setLong(6,s.getGradeid());
            ps.setDate(7, (Date) s.getEnterdate());
            ps.setString(8,s.getFromcity());
            ps.setDate(9, (Date) s.getBirthday());
            ps.setString(10,s.getAddress());
            ps.setString(11,s.getPass());
            ps.setString(12,s.getIdcardpic());
            int num = ps.executeUpdate();
            return num;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //修改
    public int update(Student s){
        Connection conn =null;
        PreparedStatement ps =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="UPDATE `s_student` SET `sname` = ?, `sex` = ?, `telephone` = ?, `qq` = ?, `wechat` = ?, `gradeid` = ?, `enterdate` = ?, `fromcity` = ?, `birthday` = ?, `address` = ?, `pass` = ?, `idcardpic` = ? WHERE `stuno` = ?";
            ps =conn.prepareStatement(sql);
            ps.setString(1,s.getSname());
            ps.setString(2,s.getSex());
            ps.setString(3,s.getTelephone());
            ps.setString(4,s.getQq());
            ps.setString(5,s.getWechat());
            ps.setLong(6,s.getGradeid());
            ps.setDate(7, (Date) s.getEnterdate());
            ps.setString(8,s.getFromcity());
            ps.setDate(9, (Date) s.getBirthday());
            ps.setString(10,s.getAddress());
            ps.setString(11,s.getPass());
            ps.setString(12,s.getIdcardpic());
            ps.setLong(13,s.getStuno());
            int num = ps.executeUpdate();
            return num;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //删除
    public int del(long stuno){
        Connection conn =null;
        PreparedStatement ps =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="delete from s_student where stuno=?";
            ps =conn.prepareStatement(sql);
            ps.setLong(1,stuno);
            int i = ps.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //查询单个学生
    public Student getStu(long stuno){
        Connection conn =null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="select * from `s_student` where `stuno` = ?";
            ps =conn.prepareStatement(sql);
            ps.setLong(1,stuno);
            rs = ps.executeQuery();
            while(rs.next()){
                Student st =new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                return st;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return null;
    }

    //分页查询
    public List<Student> limitStu(int pangNo,int pangSize){
        List<Student> list =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="select * from s_student order by stuno desc limit ?,?";
            ps =conn.prepareStatement(sql);
            ps.setInt(1,(pangNo-1)*pangSize);
            ps.setInt(2,pangSize);
            rs = ps.executeQuery();
            while (rs.next()){
                Student st =new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                list.add(st);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //模糊查询
    public List<Student> like(String name,String qq,String phone,int pangNo,int pangSize){
        List<Student> list =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        conn =DBUtil.getConnection();
        String sql ="select * from s_student where IFNULL(sname,\"\") LIKE concat('%',?,'%') and IFNULL(qq,\"\") LIKE concat('%',?,'%') and IFNULL(telephone,\"\") LIKE concat('%',?,'%') order by stuno limit ?,?";
        try {
            ps =conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,qq);
            ps.setString(3,phone);
            ps.setInt(4,(pangNo-1)*pangSize);
            ps.setInt(5,pangSize);
            rs = ps.executeQuery();
            while (rs.next()){
                Student st =new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                list.add(st);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return list;
    }

}
